package com.suixuan.support.util;

import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Calendar;

import javax.imageio.ImageIO;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFPatriarch;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * @Copyright (c) Nanjing Zhou Su co.,Ltd
 * 
 * @Project oa
 * @Title ExcelUtil.java
 * @Package com.suixuan.support.util
 * @Description 操作Excel工具类
 * @Author suixuan
 * @Editors 1
 * @Date 2010-12-31 上午11:49:32
 * @Note
 * @Version:1.0.0
 * 
 */
public class ExcelExport {

	// 定制浮点数格式
	private static String NUMBER_FORMAT = " #,##0.00 ";

	private String xlsFileName;

	private HSSFWorkbook workbook;

	private HSSFSheet sheet;

	private HSSFRow row;

	/**
	 * 工作薄中最大行数
	 */
	private int maxExcelRow = 1024;

	/**
	 * 初始化Excel
	 * 
	 * @param fileName
	 *            导出文件名
	 */
	public ExcelExport(String fileName) {
		this.xlsFileName = fileName;
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet();
	}

	/**
	 * 初始化Excel
	 * 
	 * @param fileName
	 *            导出文件名
	 * @param sheetName
	 *            工作表名
	 */
	public ExcelExport(String fileName, String sheetName) {
		this.xlsFileName = fileName;
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet(sheetName);
	}

	/**
	 * 初始化Excel
	 * 
	 * @param fileName
	 *            导出文件名
	 * @param maxExcelRow
	 *            工作薄中最大行数
	 * @param sheetName
	 *            工作表名
	 */
	public ExcelExport(String fileName, int maxExcelRow, String sheetName) {
		this.xlsFileName = fileName;
		this.maxExcelRow = maxExcelRow;
		this.workbook = new HSSFWorkbook();
		this.sheet = workbook.createSheet(sheetName);
	}

	/**
	 * 导出Excel文件
	 * 
	 * @throws XLSException
	 */
	public void exportXLS() throws ExcelException {
		try {
			FileOutputStream fileOutputStream = new FileOutputStream(xlsFileName);

			workbook.write(fileOutputStream);
			fileOutputStream.flush();
			fileOutputStream.close();
		} catch (IOException e) {
			throw new ExcelException("写入Excel文件出错!", e);
		}

	}

	/**
	 * 增加一行
	 * 
	 * @param index
	 *            行号
	 */
	public void createRow(int index) {
		if (index > this.maxExcelRow) {
			return;
		}
		this.row = this.sheet.createRow(index);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, String value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);

		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, Calendar value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellValue(value.getTime());
		// 建立新的cell样式
		HSSFCellStyle cellStyle = workbook.createCellStyle();
		// 设置cell样式为定制的日期格式
		cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat(ConstantUtils.DATE_FORMATE));
		// 设置该cell日期的显示格式
		cell.setCellStyle(cellStyle);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, int value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
	}

	/**
	 * 设置单元格
	 * 
	 * @param index
	 *            列号
	 * @param value
	 *            单元格填充值
	 */
	public void setCell(int index, double value) {
		HSSFCell cell = this.row.createCell(index);
		cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);
		cell.setCellValue(value);
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 建立新的cell样式
		HSSFDataFormat format = workbook.createDataFormat();
		cellStyle.setDataFormat(format.getFormat(NUMBER_FORMAT)); // 设置cell样式为定制的浮点数格式
		cell.setCellStyle(cellStyle); // 设置该cell浮点数的显示格式

	}

	/**
	 * 拆分单元格
	 * 
	 * @Title setMergedRegion
	 * @param firstRow
	 *            起始行
	 * @param lastRow
	 *            终止行
	 * @param firstCol
	 *            起始列
	 * @param lastCol
	 *            终止列
	 * 
	 */
	public void setMergedRegion(int firstRow, int lastRow, int firstCol, int lastCol) {
		sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
	}

	/**
	 * 添加在Excel上添加图片
	 * 
	 * @Title setImage
	 * @param imagePath
	 *            图片路径
	 * @param dx1
	 *            第一个单元格内的X坐标
	 * @param dy1
	 *            第一个单元格内的y坐标
	 * @param dx2
	 *            第二个单元格的X坐标内
	 * @param dy2
	 *            第二个单元格内的y坐标
	 * @param col1
	 *            第一个单元格的列（从0开始）
	 * @param row1
	 *            第一个单元格的行（0计算）
	 * @param col2
	 *            第二个单元格的列（从0开始）
	 * @param row2
	 *            第二个单元格的行（0计算）
	 * @throws IOException
	 * 
	 */
	public void setImage(String imagePath, int dx1, int dy1, int dx2, int dy2, short col1, int row1, short col2,
			int row2) throws ExcelException {
		ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();

		try {
			BufferedImage bufferImg = ImageIO.read(new File(imagePath));
			ImageIO.write(bufferImg, "jpg", byteArrayOut);
		} catch (IOException e) {
			throw new ExcelException(e);
		}

		HSSFPatriarch patriarch = sheet.createDrawingPatriarch();
		HSSFClientAnchor anchor = new HSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
		patriarch
				.createPicture(anchor, workbook.addPicture(byteArrayOut.toByteArray(), HSSFWorkbook.PICTURE_TYPE_JPEG));
	}

	public void setSheetName(String name) {
		workbook.setSheetName(workbook.getSheetIndex(sheet), name);
	}

	public static void main(String[] args) throws ExcelException {
		System.out.println(" 开始导出Excel文件 ");
		ExcelExport e = new ExcelExport("c:/test.xls");
		e.createRow(0);
		e.setCell(0, " 编号 ");
		e.setCell(1, " 名称 ");
		e.setCell(2, " 日期 ");
		e.setCell(3, " 金额 ");
		e.createRow(1);
		e.setCell(0, 1);
		e.setCell(1, " 工商银行 ");
		e.setCell(2, Calendar.getInstance());
		e.setCell(3, 111123.99);
		e.createRow(2);
		e.setCell(0, 2);
		e.setCell(1, " 招商银行 ");
		e.setCell(2, Calendar.getInstance());
		e.setCell(3, 222456.88);

		e.setImage("c:/1.jpg", 0, 0, 1023, 255, (short) 0, 2, (short) 0, 2);
		e.setSheetName("abc");

		try {
			e.exportXLS();
			System.out.println(" 导出Excel文件[成功] ");
		} catch (ExcelException e1) {
			System.out.println(" 导出Excel文件[失败] ");
			e1.printStackTrace();
		}
	}
}
